﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using Model;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DAccreditation
    {
        private  string strDbConnection = 
            PubConstant.GetConnectionString("dbfingerprint");

        public bool ExistPhoneNumber(string phoneNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from tdriver ");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar)
            };
            parameters[0].Value = phoneNumber;
            

            DataSet ds = DbHelperMySQL.QueryWistConnectionString(
                strDbConnection, strSql.ToString(), parameters);

            if (ds == null || ds.Tables.Count == 0)
            {
                return false;
            }
            int result = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            return result > 0;
        }

        /// <summary>
        /// 检验密码，结果为-1表示数据库错误，0表示密码有误，
        /// 1表示密码正确，2表示密码正确且未修改过
        /// </summary>
        /// <param name="cardNo"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public int CheckIdentityInfo(string cardNo,string password)
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("select count(*) from tdriver ");
            strSql1.Append(" where ServiceCardNo=@ServiceCardNo");
            MySqlParameter[] parameters1 = {
					new MySqlParameter("@ServiceCardNo", MySqlDbType.VarChar)
            };
            parameters1[0].Value = cardNo;           
            DataSet ds1 = DbHelperMySQL.QueryWistConnectionString(
                strDbConnection,strSql1.ToString(), parameters1);
            if (ds1==null || ds1.Tables.Count==0 || ds1.Tables[0].Rows.Count==0)
            {
                return -1;  //不存在服务监督卡的数据库
            }
             
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CardNo,Password from tcardpassword ");
            strSql.Append(" where CardNo=@CardNo");
            MySqlParameter[] parameters = {
					new MySqlParameter("@CardNo", MySqlDbType.VarChar)                    
            };
            parameters[0].Value = cardNo;
             
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);

            if (ds == null || ds.Tables.Count == 0)
            {
                return -1;//数据库或连接出错
            }
            if (ds.Tables[0].Rows.Count == 0)
            {
                if (password == "123456")
                {
                    return 2;
                }
                return 0;      
            }
            string strPwd = ds.Tables[0].Rows[0]["Password"].ToString();
            if (password==strPwd)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

        public Model.DriverIdentityInfo CheckIdentityWithReturn(string cardid, string password)
        {
            int checkResult = CheckIdentityInfo(cardid, password);

            if (checkResult < 1)
            {
                return null;
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tdriver ");
            strSql.Append(" where ServiceCardNo=@ServiceCardNo");
            MySqlParameter[] parameters = {
					new MySqlParameter("@ServiceCardNo", MySqlDbType.VarChar)
            };
            parameters[0].Value = cardid;        
            DataSet ds = DbHelperMySQL.QueryWistConnectionString(
                strDbConnection, strSql.ToString(), parameters);

            if (ds==null || ds.Tables.Count==0 || ds.Tables[0].Rows.Count==0)
            {
                return null;
            }
            DriverIdentityInfo model = new DriverIdentityInfo();
            if (ds.Tables[0].Rows[0]["DriverId"] != null && ds.Tables[0].Rows[0]["DriverId"].ToString() != "")
            {
                model.DriverId = int.Parse(ds.Tables[0].Rows[0]["DriverId"].ToString());
            }

            if (ds.Tables[0].Rows[0]["ServiceCardImageName"] != null && ds.Tables[0].Rows[0]["ServiceCardImageName"].ToString() != "")
            {
                model.ServiceCardImageName = ds.Tables[0].Rows[0]["ServiceCardImageName"].ToString();
            }
            if (ds.Tables[0].Rows[0]["Company"] != null && ds.Tables[0].Rows[0]["Company"].ToString() != "")
            {
                model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
            }
            if (ds.Tables[0].Rows[0]["ServiceCardNo"] != null && ds.Tables[0].Rows[0]["ServiceCardNo"].ToString() != "")
            {
                model.IDCardNo = ds.Tables[0].Rows[0]["ServiceCardNo"].ToString();
            }
            if (ds.Tables[0].Rows[0]["FullName"] != null && ds.Tables[0].Rows[0]["FullName"].ToString() != "")
            {
                model.Name = ds.Tables[0].Rows[0]["FullName"].ToString();
            }
            if (ds.Tables[0].Rows[0]["PassWord"] != null && ds.Tables[0].Rows[0]["PassWord"].ToString() != "")
            {
                model.PassWord = ds.Tables[0].Rows[0]["PassWord"].ToString();
            }
            if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
            {
                model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
            }
            if (ds.Tables[0].Rows[0]["BankCardNo"] != null && ds.Tables[0].Rows[0]["BankCardNo"].ToString() != "")
            {
                model.BankCardNo = ds.Tables[0].Rows[0]["BankCardNo"].ToString();
            }

            return model;
           
           
        }

        public Model.DriverIdentityInfo GetModelByIdCard(string cardid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tdriver ");
            strSql.Append(" where ServerCardId=@ServerCardId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@ServerCardId", MySqlDbType.VarChar)
            };
            parameters[0].Value = cardid;

            Model.DriverIdentityInfo model = new Model.DriverIdentityInfo();
            DataSet ds = DbHelperMySQL.QueryWistConnectionString(
                strDbConnection, strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {

                
                if (ds.Tables[0].Rows[0]["DriverId"] != null && ds.Tables[0].Rows[0]["DriverId"].ToString() != "")
                {
                    model.DriverId = int.Parse(ds.Tables[0].Rows[0]["DriverId"].ToString());
                }

                if (ds.Tables[0].Rows[0]["ServiceCardImageName"] != null && ds.Tables[0].Rows[0]["ServiceCardImageName"].ToString() != "")
                {
                    model.ServiceCardImageName = ds.Tables[0].Rows[0]["ServiceCardImageName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Company"] != null && ds.Tables[0].Rows[0]["Company"].ToString() != "")
                {
                    model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ServiceCardNo"] != null && ds.Tables[0].Rows[0]["ServiceCardNo"].ToString() != "")
                {
                    model.IDCardNo =ds.Tables[0].Rows[0]["ServiceCardNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FullName"] != null && ds.Tables[0].Rows[0]["FullName"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["FullName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PassWord"] != null && ds.Tables[0].Rows[0]["PassWord"].ToString() != "")
                {
                    model.PassWord = ds.Tables[0].Rows[0]["PassWord"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BankCardNo"] != null && ds.Tables[0].Rows[0]["BankCardNo"].ToString() != "")
                {
                    model.BankCardNo = ds.Tables[0].Rows[0]["BankCardNo"].ToString();
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        public Model.DriverIdentityInfo GetModelByPhoneNumber(string phoneNum)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tdriver ");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar)
            };
            parameters[0].Value = phoneNum;

            Model.DriverIdentityInfo model = new Model.DriverIdentityInfo();
            DataSet ds = DbHelperMySQL.QueryWistConnectionString(
                strDbConnection, strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {


                if (ds.Tables[0].Rows[0]["DriverId"] != null && ds.Tables[0].Rows[0]["DriverId"].ToString() != "")
                {
                    model.DriverId = int.Parse(ds.Tables[0].Rows[0]["DriverId"].ToString());
                }

                if (ds.Tables[0].Rows[0]["ServiceCardImageName"] != null && ds.Tables[0].Rows[0]["ServiceCardImageName"].ToString() != "")
                {
                    model.ServiceCardImageName = ds.Tables[0].Rows[0]["ServiceCardImageName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Company"] != null && ds.Tables[0].Rows[0]["Company"].ToString() != "")
                {
                    model.Company = ds.Tables[0].Rows[0]["Company"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ServiceCardNo"] != null && ds.Tables[0].Rows[0]["ServiceCardNo"].ToString() != "")
                {
                    model.IDCardNo = ds.Tables[0].Rows[0]["ServiceCardNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FullName"] != null && ds.Tables[0].Rows[0]["FullName"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["FullName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PassWord"] != null && ds.Tables[0].Rows[0]["PassWord"].ToString() != "")
                {
                    model.PassWord = ds.Tables[0].Rows[0]["PassWord"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["BankCardNo"] != null && ds.Tables[0].Rows[0]["BankCardNo"].ToString() != "")
                {
                    model.BankCardNo = ds.Tables[0].Rows[0]["BankCardNo"].ToString();
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        
        public bool ChangePassword(string cardNo, string oldPassword,string newPassworld)
        {
            int checkResult = CheckIdentityInfo(cardNo, oldPassword);
            
            if (checkResult<1)
            {
                return false;
            }
            int rows = 0;
            StringBuilder strSql = new StringBuilder();
            if (checkResult==1)
            {
                strSql.Append("update tcardpassword set ");
                strSql.Append("Password=@Password ");
                strSql.Append("WHERE CardNo=@CardNo ");
                MySqlParameter[] parameters = {
					new MySqlParameter("@Password", MySqlDbType.VarChar,20),				 
					new MySqlParameter("@CardNo", MySqlDbType.VarChar,15)};
                parameters[0].Value = newPassworld;
                parameters[1].Value = cardNo;
                rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            }
            else if(checkResult==2)
            {

                strSql.Append("insert into tcardpassword(");
                strSql.Append("CardNo,Password)");
                strSql.Append(" values (");
                strSql.Append("@CardNo,@Password)");
                MySqlParameter[] parameters = {
					new MySqlParameter("@CardNo", MySqlDbType.VarChar,15),
					new MySqlParameter("@Password", MySqlDbType.VarChar,15)
					 };
                parameters[0].Value = cardNo;
                parameters[1].Value = newPassworld;
                rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            }
            

          
            if (rows > 0)
            {
                return true;
            }
            return false;
        }
         
    }
}
